/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package core;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JFrame;
import javax.swing.JTable;
import util.JTableUtil;

/**
 *
 * @author Kuchcik
 */
public class Offices {
    static String[] selectedRow;
    static JFrame parent;
    public Offices(JFrame frame){
     this.parent=frame;
    }
    
    
    public static JTable getAllOffices(){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("select id as ID, nazwa as Nazwa, opis as Opis from gabinety");
            ResultSet rs = stmn.getResultSet();
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
        return null;
    }
    
    public static JTable getOffices(String name, int number_from, int number_to, String description){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            String query ="select id as ID, nazwa as Nazwa, opis as Opis from gabinety where ";
            boolean isfirstWhere=true;
            if(name!=null){
            isfirstWhere=false;
            query+="nazwa like \'%"+name+"%\'";
            }
                        
            if(isfirstWhere==false) query+=" and ";
            
            query+= "id between "+number_from+" and "+number_to;
            
            if(isfirstWhere==false && description!=null) query+=" and ";
             
            if(description!=null){
            query+="opis like \'%"+description+"%\'";
            }
            
            
            System.out.println(query);
            stmn.execute(query);
            ResultSet rs = stmn.getResultSet();
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
        return null;
    }
    
    public static void setSelectedRow(String[] row){
        Offices.selectedRow=row;
    }
    
    public static  String[] getSelectedRow(){
        return Offices.selectedRow;
    }
    
    public static boolean deleteOffice(){
            try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("delete from gabinety where id="+selectedRow[0]);
            return true;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Nie można usunąć", true,parent);
            return false;
        }
    }
    
    public static boolean addOffice(String[] row){
            try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("insert into gabinety values("+row[0]+",'"+row[1]+"','"+row[2]+"')");
            return true;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Nie można dodać(uzupełnij wszystkie miejsca)", true,parent);
            return false;
        }
    }     
}
